package batch.premium.mobillion;

import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import javax.xml.parsers.*;

import java.util.Date;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.dbutils.QueryRunner;

import org.apache.log4j.*;
import java.sql.Connection;
import java.util.List;

import java.util.*;
import java.io.*;

import batch.common.code.CodeManager;
import batch.common.db.*;
import batch.common.report.*;

public class RingtoneXMLParsing extends DefaultHandler
{
    private Connection    conn;
    private QueryRunner   qRunner;
    private String[]      site_cd;
    private String        log_path;
    private String        file_nm;
    private String        strDate;

    private StringBuffer  strSql       = new StringBuffer();
    private StringBuffer  strError     = new StringBuffer();

    private long          id_count = 0;  //contents_id seq
    private String[]      contents_value;
    private List          ltData;

    private boolean       processYN = false;

    final String CP_ID         = "0001";
    final String MCATE_ID      = "06";
    final String TODAY         = DateFormatUtils.format(new Date(), "yyyyMMdd");
    final String STARTDATE     = DateFormatUtils.format(new Date(), "yyyyMMddHHmmss");
    final String STRSUBJECT    = "Mobillion Ringtone[06] Batch Error(" + TODAY + ")";
    final String CONTENTS_PATH = "/home01/sfc/batch/premium/data/mobillion/ringtone/contents/";

    public RingtoneXMLParsing(){
        ;
    }

    public static void main( String[] args ){
        RingtoneXMLParsing xmlP = new RingtoneXMLParsing();
        xmlP.site_cd   = StringUtils.split(args[0], "/");
        xmlP.strDate   = args[1];

        Logger.getLogger("mobillion.ringtone").info("( * ) Start.");
        xmlP.xmlParsing_start();
        Logger.getLogger("mobillion.ringtone").info("( * ) End.");
    }

    public void xmlParsing_start(){
        log_path = "/home01/sfc/batch/premium/data/mobillion/ringtone/sh/" + strDate + "/";
        file_nm  = "/home01/sfc/batch/premium/data/mobillion/ringtone/xml/ringtone_0001_" + strDate + ".xml";

        try{
            ltData = new LinkedList();
            Logger.getLogger("mobillion.ringtone").info("( * ) Start parsing.");
            SAXParserFactory  factory = SAXParserFactory.newInstance( );
            factory.setNamespaceAware (true);
            SAXParser parser = factory.newSAXParser( );
            parser.parse(file_nm, this);
            Logger.getLogger("mobillion.ringtone").info("( * ) End parsing.");

            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);

            //To delete the previous Data
            Logger.getLogger("mobillion.ringtone").info("( * ) Start Deleted data.");
            for(int i=0;i<site_cd.length;i++){
            	String[] strParams = new String[]{site_cd[i], CP_ID, MCATE_ID};
            	
            	deleteData(conn, strParams);
            	deletePhone(conn, strParams);
            	deletePhoneDisplay(conn, strParams);
            }
            Logger.getLogger("mobillion.ringtone").info("( * ) End Deleted data.");
            int nInserted = 0;

            //To make Today's Data
            Logger.getLogger("mobillion.ringtone").info("( * ) Start Data insert.");
            for(Iterator i = ltData.iterator(); i.hasNext();) {
                String[] temp = (String[])i.next();

                //for(int j=0;j<temp.length;j++) System.out.println("temp[" + j + "]==" + temp[j]);

                if(existFile(temp)){
                    addData(conn, temp);
                    addPhone(conn, temp);
                    shellFileWrite(temp);

                    nInserted++;
                }
            }
            Logger.getLogger("mobillion.ringtone").info("( * ) End Data insert.");

            // select phone list insert
            CodeManager codeMgr = new CodeManager();
            String[] lang_cd = null;
            Logger.getLogger("mobillion.ringtone").info("( * ) Start Display Phone data delete & insert.");
            for(int i=0;i<site_cd.length;i++){
                lang_cd = codeMgr.getLangs(site_cd[i]);
            	for(int j = 0; j < lang_cd.length; j++) {
		            //insert cp_phoneDis
            		addPhoneDisplay(conn, site_cd[i], lang_cd[j]);      
	            }
            }
            Logger.getLogger("mobillion.ringtone").info("( * ) End Display Phone data delete & insert.");
            
            conn.commit();
            Logger.getLogger("mobillion.ringtone").info("Success. " + nInserted + " inserted. ");
        }
        catch(Exception ex){
            if (conn != null) try { conn.rollback(); } catch (Exception ee) {}

            strError.setLength(0);
            strError.append("---------------------  " + STRSUBJECT + "  ---------------------\n")
                    .append("file name :        " + file_nm + "                              \n")
                    .append(ex.getMessage() + "                                              \n")
                    .append("--------------------------------------------------------------- \n");
            Logger.getLogger("mobillion.ringtone").info(strError.toString());

            //processFail(CONTENTS_PATH);  //Delete Images
            processFail(log_path);       //Delete Shell Files
            sendErrMail(strError);
        }
        finally{
            if(conn != null) try { conn.close(); } catch(Exception ex) {}
        }
    }

    public void startDocument() throws SAXException{
        ;
    }

    public void endDocument() throws SAXException {
        ;
    }

    /*****************************************************************************/
    /* name : startElement                                                       */
    /* argument : String uri :                                                   */
    /*            String localName :            */
    /*            String qName :                      			 */
    /*            Attributes atts :              		 */
    /* return :                                                                  */
    /* description :                                                             */
    /* process :  String[] contents_value : data                                 */
    /*            contents_value[0] : site_cd                                    */
    /*            contents_value[1] : lang_cd                                    */
    /*            contents_value[2] : cp_id                                      */
    /*            contents_value[3] : mcate_cd                                   */
    /*            contents_value[4] : scate_cd                                   */
    /*            contents_value[5] : cpcontents_id                              */
    /*            contents_value[6] : contents_id                                */
    /*            contents_value[7] : title_nm                                   */
    /*            contents_value[8] : contents_desc                              */
    /*            contents_value[9] : preview_file_nm                            */
    /*            contents_value[10]: point_num                                  */
    /*            contents_value[11]: point_yn                                   */
    /*            contents_value[12]: visible_cd                                 */
    /*            contents_value[13]: ordercode                                  */
    /*            contents_value[14]: order_url                                  */
    /*            contents_value[15]: artist_nm                                  */
    /*            contents_value[16]: availablephone                             */
    /*            contents_value[17]: ivr_price                                  */
    /* etc :                                                                     */
    /*****************************************************************************/
    public void startElement(String uri, String localName, String qName, Attributes atts) throws SAXException{
        if(qName.equals("ringtone")){
            String xml_site_cd = atts.getValue("site_cd");

            for(int i=0;i<site_cd.length;i++){
                if(xml_site_cd.compareToIgnoreCase(site_cd[i]) == 0){
                    processYN = true;
                    contents_value = new String[18];
                    contents_value[0]  = xml_site_cd.toUpperCase();
                    contents_value[1]  = atts.getValue("lang_cd");
                    contents_value[2]  = atts.getValue("cp_id");
                    contents_value[3]  = atts.getValue("mcate_cd");
                    contents_value[4]  = atts.getValue("scate_cd");
                    contents_value[5]  = atts.getValue("contents_id");
                    //contents_value[6]  = DateFormatUtils.format(new Date(), "MMddHHmmss") + getCount();
                    contents_value[6]  = atts.getValue("contents_id").trim();
                    contents_value[7]  = atts.getValue("title_nm");
                    contents_value[8]  = atts.getValue("contents_desc");
                    contents_value[9]  = atts.getValue("preview_file_nm");
                    contents_value[10] = atts.getValue("point_num");
                    contents_value[11] = atts.getValue("point_yn");
                    contents_value[12] = atts.getValue("visible_cd");
                    contents_value[13] = atts.getValue("ordercode");
                    contents_value[14] = atts.getValue("order_url");
                    contents_value[15] = atts.getValue("artist_nm");
                    contents_value[16] = atts.getValue("availablephone");
                    contents_value[17] = atts.getValue("ivr_price");
                }
            }
        }
    }

    public void endElement(String uri,String localName, String qName) throws SAXException{
        if(localName.equals("ringtone") && processYN){
            for(int i=0;i<contents_value.length;i++){
                contents_value[i] = StringUtils.defaultString(contents_value[i]);
            }

            ltData.add(contents_value);

            processYN = false;
        }
    }

    /*****************************************************************************/
    /* name : characters()                                                       */
    /* argument : char[] ch :                                              */
    /*            int start :                           */
    /*            int len :                   */
    /* return :                                                                  */
    /* description :                                                             */
    /* etc :                                                                     */
    /*****************************************************************************/
    public void characters( char[] ch, int start, int len ){
        ;
    }

    public void ignorableWhitespace(char ch[], int start, int length ) throws SAXException{
        ;
    }

    public void addData(Connection conn, String[] add_contents_value) throws Exception{
        int iRE = 0;
        strSql.setLength(0);

        try{
            qRunner = new QueryRunner();

            strSql.append("INSERT INTO G_" + add_contents_value[0] + "_PL_CPRINGTONE \n")
                  .append("          ( CP_ID                                         \n")
                  .append("          , MCATE_CD                                      \n")
                  .append("          , LANG_CD                                       \n")
                  .append("          , SCATE_CD                                      \n")
                  .append("          , CONTENTS_ID                                   \n")
                  .append("          , CPCONTENTS_ID                                 \n")
                  .append("          , TITLE_NM                                      \n")
                  .append("          , CONTENTS_DESC                                 \n")
                  .append("          , PREVIEW_FILE_NM                               \n")
                  .append("          , POINT_NUM                                     \n")
                  .append("          , POINT_YN                                      \n")
                  .append("          , VISIBLE_CD                                    \n")
                  .append("          , WAPPUSH_URL                                   \n")
                  .append("          , ORDER_CD                                      \n")
                  .append("          , ARTIST_NM                                     \n")
                  .append("          , IVR_PRICE)                                    \n")
                  .append("    VALUES( ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?                                             \n")
                  .append("          , ?)                                            \n");

            ArrayList params = new ArrayList();

            params.add(add_contents_value[2]);   //CP_ID
            params.add(add_contents_value[3]);   //MCATE_CD
            params.add(add_contents_value[1]);   //LANG_CD
            params.add(add_contents_value[4]);   //SCATE_CD
            params.add(add_contents_value[6]);   //CONTENTS_ID
            params.add(add_contents_value[5]);   //CPCONTENTS_ID
            params.add(add_contents_value[7]);   //TITLE_NM
            params.add(add_contents_value[8]);   //CONTENTS_DESC
            params.add(add_contents_value[9]);   //PREVIEW_FILE_NM
            params.add(add_contents_value[10]);  //POINT_NUM
            params.add(add_contents_value[11]);  //POINT_YN
            params.add(add_contents_value[12]);  //VISIBLE_CD
            params.add(add_contents_value[14]);  //WAPPUSH_URL
            params.add(add_contents_value[13]);  //ORDER_CODE
            params.add(add_contents_value[15]);  //ARTIST_NM
            params.add(add_contents_value[17]);  //IVR_PRICE

            iRE = qRunner.update(conn, strSql.toString(), params.toArray());

            if(iRE != 1){
                throw new Exception("CP Insert Error");
            }
        }
        finally{
            ;
        }
    }

    public void addPhone(Connection conn, String[] add_contents_value) throws Exception{
        int iRE = 0;
        String[] availablephone = StringUtils.split(add_contents_value[16], "/");
        int size = (availablephone == null) ? 0 : availablephone.length;
        strSql.setLength(0);

        try{
            qRunner = new QueryRunner();

            strSql.append("INSERT INTO G_" + add_contents_value[0] + "_PL_CPPHONE \n")
                  .append("          ( CP_ID                                      \n")
                  .append("          , MCATE_CD                                   \n")
                  .append("          , LANG_CD                                    \n")
                  .append("          , SCATE_CD                                   \n")
                  .append("          , CONTENTS_ID                                \n")
                  .append("          , PHONE)                                     \n")
                  .append("    VALUES( ?                                          \n")
                  .append("          , ?                                          \n")
                  .append("          , ?                                          \n")
                  .append("          , ?                                          \n")
                  .append("          , ?                                          \n")
                  .append("          , ?)                                         \n");

            for(int i=0;i<size;i++){
                ArrayList params = new ArrayList();

                params.add(add_contents_value[2]);       //CP_ID
                params.add(add_contents_value[3]);       //MCATE_CD
                params.add(add_contents_value[1]);       //LANG_CD
                params.add(add_contents_value[4]);       //SCATE_CD
                params.add(add_contents_value[6]);       //CONTENTS_ID
                params.add(availablephone[i]);           //PHONE

                iRE = qRunner.update(conn, strSql.toString(), params.toArray());

                if(iRE != 1){
                    throw new Exception("Phone Insert Error");
                }
            }
        }
        finally{
            ;
        }
    }
    
    public void addPhoneDisplay(Connection conn, String strSite_cd, String strLang_cd) throws Exception{
        strSql.setLength(0);

        try{
            qRunner = new QueryRunner();

            strSql.append("	INSERT INTO G_" + strSite_cd + "_PL_CPPHONEDISPLAY		\n");
            strSql.append("		(CP_ID, MCATE_CD, LANG_CD, PHONE )              	\n");
            strSql.append("	    (                                               	\n");
            strSql.append("		SELECT                                          	\n");
            strSql.append("	    	'"+CP_ID+"' CP_ID, '"+MCATE_ID+"' MCATE_CD  	\n");
            strSql.append("			, '"+strLang_cd+"' LANG_CD, PHONE           	\n");
            strSql.append("		FROM                                            	\n");
            strSql.append("			(                                           	\n");
            strSql.append("			SELECT                                      	\n");
            strSql.append("				DISTINCT PHONE                          	\n");
            strSql.append("			FROM G_" + strSite_cd + "_PL_CPPHONE            \n");
            strSql.append("			WHERE CP_ID = '"+CP_ID+"'                   	\n");
            strSql.append("				AND MCATE_CD = '"+MCATE_ID+"'           	\n");
            strSql.append("				AND LANG_CD = '"+strLang_cd+"'          	\n");
            strSql.append("			)                                           	\n");
            strSql.append("		)                                               	\n");

            qRunner.update(conn, strSql.toString(), null);
        }
        finally{
            ;
        }
    }

    public void deleteData(Connection conn, String[] strParams) throws Exception{
        strSql.setLength(0);

        try{
            qRunner = new QueryRunner();

            strSql.append("DELETE FROM G_" + strParams[0] + "_PL_CPRINGTONE \n")
                  .append(" WHERE CP_ID    = '" + strParams[1] + "'         \n")
                  .append("   AND MCATE_CD = '" + strParams[2] + "'         \n")
                  .append("   AND  CREATED_YMDHMS  " + strParams[3] + "          \n");

            qRunner.update(conn, strSql.toString());
            Logger.getLogger("mobillion.ringtone").info(strParams[0] + " deleteData");
        }
        finally{
            //System.out.println("=====================================================1");
        }
    }

    public void deletePhone(Connection conn, String[] strParams) throws Exception{
        strSql.setLength(0);

        try{
            qRunner = new QueryRunner();

            strSql.append("DELETE FROM G_" + strParams[0] + "_PL_CPPHONE \n")
                  .append(" WHERE CP_ID    = '" + strParams[1] + "'      \n")
                  .append("   AND MCATE_CD = '" + strParams[2] + "'      \n")
                  .append("   AND  CREATED_YMDHMS  " + strParams[3] + "       \n");

            qRunner.update(conn, strSql.toString());
            Logger.getLogger("mobillion.ringtone").info(strParams[0] + " deletePhone");

        }
        finally{
            //System.out.println("=====================================================2");
        }
    }
    
    public void deletePhoneDisplay(Connection conn, String[] strParams) throws Exception{
        strSql.setLength(0);
        try{
            qRunner = new QueryRunner();
            
            strSql.append("DELETE FROM G_" + strParams[0] + "_PL_CPPHONEDISPLAY \n")
                  .append(" WHERE CP_ID    = '" + strParams[1] + "'      	\n")
                  .append("   AND MCATE_CD = '" + strParams[2] + "'      	\n");
            qRunner.update(conn, strSql.toString());
            Logger.getLogger("mobillion.ringtone").info(strParams[0] + " deletePhoneDisplay");
        }
        
        finally{
            //System.out.println("=====================================================2");
        }
    }
    
    public boolean existFile(String[] add_contents_value) throws Exception{
        if(!new File(CONTENTS_PATH + add_contents_value[9]).exists()){
            strError.setLength(0);
            strError.append("-----------------  File not found  ------------------ \n")
                    .append("contents_id     :  " + add_contents_value[5] + "      \n")
                    .append("mcate_cd        :  " + add_contents_value[3] + "      \n")
                    .append("title_nm        :  " + add_contents_value[7] + "      \n")
                    .append("preview_file_nm :  " + add_contents_value[9] + "      \n")
                    .append("------------------------------------------------------\n");
            Logger.getLogger("mobillion.ringtone").info(strError.toString());

            return false;
        }

        return true;
    }

    public void shellFileWrite(String[] add_contents_value) throws Exception{
        String nas_path = "";
        FileWriter fw = null;

        try{
            String lwSite_cd = add_contents_value[0].toLowerCase();

            nas_path = "/home02/nas/" + lwSite_cd + "_pub/adm/cp/" + CP_ID + "/" + add_contents_value[3] + "/";

            fw = new FileWriter(log_path + add_contents_value[0] + "_" + strDate + ".sh", true);

            fw.write("cp \"" + add_contents_value[9]  + "\" \"" + nas_path + add_contents_value[9]  + "\" \n");
        }
        finally{
            if( fw != null ) try { fw.close(); } catch(Exception ex) {}
        }
    }

    public void processFail(String filePath){
        try{
            File[] delFiles = new File(filePath).listFiles();
            for (int i = 0; i < delFiles.length; i++) {
                if (!delFiles[i].isDirectory() && delFiles[i].exists())
                    delFiles[i].delete();
            }
        }
        catch(Exception ex){
            Logger.getLogger("mobillion.ringtone").info(ex.getMessage());
        }
    }

    public void sendErrMail(StringBuffer strMsg){
        ReportError.byEmail(STRSUBJECT, strMsg.toString());
    }

    public String getCount(){
        id_count++;

        if(id_count == 10000000){
            id_count = 0;
        }

        String strIdCount = String.valueOf(id_count);

        switch(strIdCount.length()){
            case 1 : strIdCount = "000000" + id_count; break;
            case 2 : strIdCount = "00000"  + id_count; break;
            case 3 : strIdCount = "0000"   + id_count; break;
            case 4 : strIdCount = "000"    + id_count; break;
            case 5 : strIdCount = "00"     + id_count; break;
            case 6 : strIdCount = "0"      + id_count; break;
        }

        return strIdCount;
    }
}
